# 数据库读取函数
import pymysql


def search_major(major):
    # 1 创建数据库连接
    con = pymysql.connect(
        host="localhost",
        user="root",
        passwd="20011231ZENG",
        database="pycharm",
        charset="utf8",
        port=3306
    )
    # 2 获取游标，操作数据库的对象
    cur = con.cursor()
    # 3 定义查询
    sql = "select name, class from ranking where major=%s"
    # 4 编译sql
    cur.execute(sql, major)
    # 5 获取查询结果
    rs = cur.fetchall()
    # 6 关闭链接
    cur.close()
    con.close()
    return rs


def search_all():
    # 1 创建数据库连接
    con = pymysql.connect(
        host="localhost",
        user="root",
        passwd="20011231ZENG",
        database="pycharm",
        charset="utf8",
        port=3306
    )
    # 2 获取游标，操作数据库的对象
    cur = con.cursor()
    # 3 定义查询
    sql = "select name, class from ranking "
    # 4 编译sql
    cur.execute(sql)
    # 5 获取查询结果
    rs = cur.fetchall()
    # 6 关闭链接
    cur.close()
    con.close()
    return rs


def search_average_compulsory_by_sex():
    # 1 创建数据库连接
    con = pymysql.connect(
        host="localhost",
        user="root",
        passwd="20011231ZENG",
        database="pycharm",
        charset="utf8",
        port=3306
    )
    # 2 获取游标，操作数据库的对象
    cur = con.cursor()
    # 3 定义查询
    sql = "select sex, average_compulsory from ranking "
    # 4 编译sql
    cur.execute(sql)
    # 5 获取查询结果
    rs = cur.fetchall()
    # 6 关闭链接
    cur.close()
    con.close()
    return rs


def search_average_compulsory_by_major(major):
    # 1 创建数据库连接
    con = pymysql.connect(
        host="localhost",
        user="root",
        passwd="20011231ZENG",
        database="pycharm",
        charset="utf8",
        port=3306
    )
    # 2 获取游标，操作数据库的对象
    cur = con.cursor()
    # 3 定义查询
    sql = "select class, average_compulsory, total_credits from ranking where major=%s"
    # 4 编译sql
    cur.execute(sql, major)
    # 5 获取查询结果
    rs = cur.fetchall()
    # 6 关闭链接
    cur.close()
    con.close()
    return rs


def search_average_all_courses():
    # 1 创建数据库连接
    con = pymysql.connect(
        host="localhost",
        user="root",
        passwd="20011231ZENG",
        database="pycharm",
        charset="utf8",
        port=3306
    )
    # 2 获取游标，操作数据库的对象
    cur = con.cursor()
    # 3 定义查询
    sql = "select average_all_courses from ranking "
    # 4 编译sql
    cur.execute(sql)
    # 5 获取查询结果
    rs = cur.fetchall()
    # 6 关闭链接
    cur.close()
    con.close()
    return rs
